package cn.stylefeng.guns.sys.modular.dbservicedetails.service.impl;

import cn.stylefeng.guns.base.auth.context.LoginContextHolder;
import cn.stylefeng.guns.base.auth.model.LoginUser;
import cn.stylefeng.guns.base.pojo.page.LayuiPageFactory;
import cn.stylefeng.guns.base.pojo.page.LayuiPageInfo;
import cn.stylefeng.guns.sys.modular.dbServicelist.model.params.DbServicelistParam;
import cn.stylefeng.guns.sys.modular.dbServicelist.model.result.DbServicelistResult;
import cn.stylefeng.guns.sys.modular.dbservicedetails.entity.DbServicedetails;
import cn.stylefeng.guns.sys.modular.dbservicedetails.entity.TableDetails;
import cn.stylefeng.guns.sys.modular.dbservicedetails.mapper.DbServicedetailsMapper;
import cn.stylefeng.guns.sys.modular.dbservicedetails.model.params.DbServicedetailsParam;
import cn.stylefeng.guns.sys.modular.dbservicedetails.model.result.DbServicedetailsResult;
import cn.stylefeng.guns.sys.modular.dbservicedetails.service.DbServicedetailsService;
import cn.stylefeng.roses.core.util.ToolUtil;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import org.springframework.stereotype.Service;

import java.io.Serializable;
import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * <p>
 * 服务详情  服务实现类
 * </p>
 *
 * @author zhaoyiheng
 * @since 2020-08-21
 */
@Service
public class DbServicedetailsServiceImpl extends ServiceImpl<DbServicedetailsMapper, DbServicedetails> implements DbServicedetailsService {

    @Override
    public void add(DbServicedetailsParam param, LoginUser user) {
        DbServicedetails entity = getEntity(param);
        entity.setCreatedBy(user.getUsername());
        entity.setCreatedTime(new Date());
        entity.setUpdatedBy(user.getUsername());
        entity.setUpdatedTime(new Date());
        this.save(entity);
    }

    @Override
    public void delete(DbServicedetailsParam param) {
        this.removeById(getKey(param));
    }

    @Override
    public void update(DbServicedetailsParam param) {
        DbServicedetails oldEntity = getOldEntity(param);
        DbServicedetails newEntity = getEntity(param);
        ToolUtil.copyProperties(newEntity, oldEntity);
        this.updateById(newEntity);
    }

    @Override
    public DbServicedetailsResult findBySpec(DbServicedetailsParam param) {
        return null;
    }

    @Override
    public List<DbServicedetailsResult> findListBySpec(DbServicedetailsParam param) {
        return this.baseMapper.customList(param);
    }

    @Override
    public LayuiPageInfo findPageBySpec(DbServicedetailsParam param) {
        Page pageContext = getPageContext();
        IPage page = this.baseMapper.customPageList(pageContext, param);
        return LayuiPageFactory.createPageInfo(page);
    }

    @Override
    public JSONObject queryTableDetails(DbServicelistParam param, String tableName, Integer page, Integer limit) {
        //TODO 点击表格某行获取该行表格详细信息
        //获取当前表所在数据库信息
        String username = param.getUsername();
        String password = param.getPassword();
        String dbType = param.getDbType();
        String driver = "";
        String url = "";
        //mysql数据库和Oracle数据库分别处理
        //声明连接
        Connection conn = null;
        Connection conn1 = null;
        if (dbType.equals("1")) {
            //驱动程序名
            driver = "com.mysql.cj.jdbc.Driver";
            //URL指向要访问的数据库名mydata
            url = "jdbc:mysql://" + param.getDbUrl() + ":" + param.getDbNumber() + "/" + param.getDbName() + "?autoReconnect=true&useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=CONVERT_TO_NULL&useSSL=false&serverTimezone=CTT";
        } else if (dbType.equals("2")) {
            //驱动程序名
            driver = "oracle.jdbc.OracleDriver";
            //URL指向要访问的数据库名mydata
            url = "jdbc:oracle:thin:@" + param.getDbUrl() + ":" + param.getDbNumber() + "/" + param.getDbName();
        }
        //遍历查询结果集
        ResultSet rs = null;
        ResultSet rs1 = null;
        //数据个数
        int dataNumber = 0;
        JSONObject obj = new JSONObject();
        try {
            //加载驱动程序
            Class.forName(driver);
            //1.getConnection()方法，连接MySQL数据库！！
            conn = DriverManager.getConnection(url, username, password);
            //执行的sql获取该数据库下点击表的详细信息
            String sql = "select COLUMN_NAME,IS_NULLABLE,DATA_TYPE,COLUMN_COMMENT from information_schema.columns where table_schema =" + "\'" + param.getDbName() + "\'" + " and table_name =" + "\'" + tableName + "\'" + "limit " + (page - 1) * limit + "," + limit;
            Statement stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            // 获取列数
            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();
            conn1 = DriverManager.getConnection(url, username, password);
            String sql1 = "select count(a.COLUMN_NAME) from ( select COLUMN_NAME,IS_NULLABLE,DATA_TYPE,COLUMN_COMMENT from information_schema.columns where table_schema =" + "\'" + param.getDbName() + "\'" + " and table_name =" + "\'" + tableName + "\'" + "limit " + (page - 1) * limit + "," + limit + ") AS a";
            Statement stmt1 = conn1.createStatement();
            rs1 = stmt1.executeQuery(sql1);
            //遍历获取到总数据个数
            while (rs1.next()) {
                dataNumber = rs1.getInt(1);
            }
            // 遍历ResultSet中的每条数据
            obj.put("code", 0);
            obj.put("msg", "获取成功");
            obj.put("count", dataNumber);
            JSONArray array = new JSONArray();
            while (rs.next()) {
                // 遍历每一列
                JSONObject jsonObj = new JSONObject();
                for (int i = 1; i <= columnCount; i++) {
                    String value = null;
                    String columnName = metaData.getColumnLabel(i);//列名称
                    if (rs.getString(columnName) != null && !rs.getString(columnName).equals("")) {
                        value = new String(rs.getBytes(columnName), "UTF-8");//列的值,有数据则转码
                        //  System.out.println("===" + value);
                    } else {
                        value = "";//列的值，为空，直接取出去
                    }
                    jsonObj.put(columnName, value);
                }
                array.add(jsonObj);
                obj.put("data", array);
            }
            return obj;
        } catch (SQLException | ClassNotFoundException e) {
            obj = new JSONObject();
            System.out.println("获取表名失败");
            obj.put("code", 0);
            obj.put("msg", "获取失败");
        } finally {
            try {
                rs.close();
                conn.close();
            } catch (SQLException e) {
                System.out.println("关闭连接失败");
                obj = new JSONObject();
                obj.put("code", 0);
                obj.put("msg", "关闭连接失败");
            }
            return obj;
        }
    }

    private Serializable getKey(DbServicedetailsParam param) {
        return param.getId();
    }

    private Page getPageContext() {
        return LayuiPageFactory.defaultPage();
    }

    private DbServicedetails getOldEntity(DbServicedetailsParam param) {
        return this.getById(getKey(param));
    }

    private DbServicedetails getEntity(DbServicedetailsParam param) {
        DbServicedetails entity = new DbServicedetails();
        ToolUtil.copyProperties(param, entity);
        return entity;
    }

}
